package com.lyouu.dataminer.cruisetour;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.db.DbLyouuParams;
import com.lyouu.dataminer.factory.DaoFactory;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.util.CategorizerUtil;
import com.lyouu.dataminer.util.Constants;
import com.lyouu.dataminer.util.TourMap;
import com.lyouu.dataminer.util.Util;

public class CTKaiyuanDe {
	private static final String CURRENT_YEAR = String.valueOf(Calendar
			.getInstance().get(Calendar.YEAR));
	private Logger logger = Logger.getLogger(CTKaiyuanDe.class);
	private Connection conn;
	private String currency;

	// lyouu_src db, bt_kaiyuan_de_brief table
	private ArrayList<String> bPageUrlList;
	private ArrayList<String> bRouteList;
	private ArrayList<String> bImgUrlList;
	private ArrayList<String> bContentUrlList;
	// lyouu_src db, bt_kaiyuan_de_content table
	private ArrayList<String> cTitleList;
	private ArrayList<String> cPageUrlList;
	private ArrayList<String> cHighlightList;
	private ArrayList<String> cDepartCityList;
	private ArrayList<String> cScheduleList;
	private ArrayList<String> cGroupIdList;
	private ArrayList<String> cTravelDateList;
	private ArrayList<String> cPriceList;
	private ArrayList<String> cStatusList;
	private ArrayList<String> cOrderUrlList;

	private ArrayList<TourBrief> tourBriefList;

	public CTKaiyuanDe() {
		this.bPageUrlList = new ArrayList<String>();// for test
		this.bRouteList = new ArrayList<String>();
		this.bContentUrlList = new ArrayList<String>();
		this.bImgUrlList = new ArrayList<String>();

		this.cTitleList = new ArrayList<String>();
		this.cPageUrlList = new ArrayList<String>();
		this.cHighlightList = new ArrayList<String>();
		this.cDepartCityList = new ArrayList<String>();
		this.cScheduleList = new ArrayList<String>();
		this.cGroupIdList = new ArrayList<String>();
		this.cTravelDateList = new ArrayList<String>();
		this.cPriceList = new ArrayList<String>();
		this.cStatusList = new ArrayList<String>();
		this.cOrderUrlList = new ArrayList<String>();
	}

	public void getData() throws SQLException {
		conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU_SRC);
		String sql = "SELECT * FROM ct_kaiyuan_de_brief";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			bPageUrlList.add(rs
					.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PAGE_URL));
			bImgUrlList.add(rs
					.getString(DbConstants.LYOUU_SRC_BUS_TOUR_IMG_URL));
			bRouteList.add(Util
					.replaceHtmlCodeToSpecialCharacter(formatDepartCityStr(rs
							.getString(DbConstants.LYOUU_SRC_BUS_TOUR_ROUTE))));
			bContentUrlList.add(rs
					.getString(DbConstants.LYOUU_SRC_BUS_TOUR_CONTENT_URL));
		}
		tourBriefList = generateTourBriefList(bPageUrlList, bContentUrlList,
				bRouteList, bImgUrlList);

		String title;
		int i;
		sql = "SELECT * FROM bt_kaiyuan_de_content";
		rs = stmt.executeQuery(sql);
		while (rs.next()) {
			title = rs.getString(DbConstants.LYOUU_SRC_BUS_TOUR_TITLE);
			for (i = 0; i < Constants.NO_EUROPE_BUS_TOUR_KEYWORD_ARY.length; i++) {
				if (title.contains(Constants.NO_EUROPE_BUS_TOUR_KEYWORD_ARY[i]))
					break;
			}
			if (i == Constants.NO_EUROPE_BUS_TOUR_KEYWORD_ARY.length) {
				cTitleList.add(title);
				cPageUrlList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PAGE_URL));
				cHighlightList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_HIGHLIGHT));
				cDepartCityList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_DEPART_CITY));
				cScheduleList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_SCHEDULE));
				cGroupIdList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_GROUP_ID));
				cTravelDateList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_TRAVEL_DATE));
				cPriceList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_PRICE));
				cStatusList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_STATUS));
				cOrderUrlList.add(rs
						.getString(DbConstants.LYOUU_SRC_BUS_TOUR_ORDER_URL));
			}
		}
		conn.close();
	}

	public void printData() {
		OutputStream os = null;
		try {
			os = new FileOutputStream("Z:\\Downloads\\ct_kaiyuan_de.txt");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		final PrintStream ps = new PrintStream(os);

		ps.println("Tour Brief List:");
		for (int i = 0; i < tourBriefList.size(); i++) {
			ps.println(i + 1);
			ps.println(tourBriefList.get(i).getContentUrl());
			ps.println(tourBriefList.get(i).getRoute());
		}

		for (int i = 0; i < cTitleList.size(); i++) {
			ps.println(i + 1);
			ps.println((i + 1) + ":\t" + cTitleList.get(i) + "\t"
					+ cPageUrlList.get(i));
			ps.println(cHighlightList.get(i));
			ps.println(cDepartCityList.get(i));
			ps.println(cScheduleList.get(i));
			ps.println(cGroupIdList.get(i));
			ps.println(cTravelDateList.get(i));
			ps.println(cPriceList.get(i));
			ps.println(cStatusList.get(i));
			ps.println(cOrderUrlList.get(i));
			ps.println("\n\n");
		}
		ps.close();
		try {
			os.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateDbLyouu() throws SQLException {
		String name, imgUrl, content, departCity, routine, highlight, departCountry;
		// bustour_schedule table
		ArrayList<String> btsGroupIdList = new ArrayList<String>();
		ArrayList<String> btsDepartDateList = new ArrayList<String>();
		ArrayList<String> btsEndDateList = new ArrayList<String>();
		ArrayList<String> btsAdultPriceList = new ArrayList<String>();
		ArrayList<String> btsChildPriceList = new ArrayList<String>();
		ArrayList<String> btsGroupPriceList = new ArrayList<String>();
		ArrayList<String> btsStatusList = new ArrayList<String>();
		ArrayList<String> btsBookingList = new ArrayList<String>();

		for (int i = 0; i < cTitleList.size(); i++) {
			name = parseName(cTitleList.get(i));
			logger.info("\n" + (i + 1) + ": " + name + "  "
					+ cPageUrlList.get(i));
			content = parseContent(cScheduleList.get(i));
			imgUrl = Constants.NO_THUMB;
			routine = "";
			departCity = "";
			for (int j = 0; j < tourBriefList.size(); j++) {
				if (cPageUrlList.get(i).equals(
						tourBriefList.get(j).getContentUrl())) {
					routine = parseRoutine(tourBriefList.get(j).getRoute());
					imgUrl = parseImgUrl(tourBriefList.get(j).getImgUrl());
				}
			}
			departCity = parseDepartCity(cDepartCityList.get(i));
			highlight = parseHighlight(cHighlightList.get(i));
			departCountry = parseDepartCountry(name);
			parseGroupId(cGroupIdList.get(i), btsGroupIdList);
			parsePrice(cPriceList.get(i), btsAdultPriceList, btsChildPriceList,
					btsGroupPriceList);
			parseTravelDate(cTravelDateList.get(i), btsDepartDateList,
					btsEndDateList);
			parseStatus(cStatusList.get(i), btsStatusList);
			parseBooking(cOrderUrlList.get(i), btsBookingList);
			// logger.info(departCountry);
			// update db_lyouu
			CruiseTour.insertCruiseTour(cPageUrlList.get(i),
					Constants.OWNER_DE_KAIYUAN, name, imgUrl, content,
					departCity, routine, this.currency,
					Util.getMinPrice(btsAdultPriceList),
					Constants.PROVIDER_DE_KAIYUAN,
					Util.getMinPrice(btsChildPriceList),
					Util.getMinPrice(btsGroupPriceList), departCountry,
					btsDepartDateList, btsEndDateList, highlight, "");

			CruiseTour.insertCruisetourSchedule(Constants.OWNER_DE_KAIYUAN, name,
					btsGroupIdList, btsDepartDateList, btsEndDateList,
					this.currency, btsAdultPriceList, btsChildPriceList,
					btsGroupPriceList, btsStatusList, btsBookingList,
					cPageUrlList.get(i));
		}
	}

	private ArrayList<TourBrief> generateTourBriefList(
			ArrayList<String> bPageUrlList, ArrayList<String> contentUrlList,
			ArrayList<String> routeList, ArrayList<String> imgUrlList) {
		ArrayList<TourBrief> tbList = new ArrayList<TourBrief>();
		if (contentUrlList.size() == routeList.size()) {
			String[] contentUrlAry;
			String[] routeAry;
			String[] imgUrlAry;
			for (int i = 0; i < contentUrlList.size(); i++) {
				contentUrlAry = contentUrlList.get(i)
						.split(Constants.DELIMITER);
				routeAry = routeList.get(i).split(Constants.DELIMITER);
				imgUrlAry = imgUrlList.get(i).split(Constants.DELIMITER);
				if (contentUrlAry.length == routeAry.length) {
					for (int j = 0; j < contentUrlAry.length; j++)
						tbList.add(new TourBrief(contentUrlAry[j], routeAry[j],
								imgUrlAry[j]));
				} else {
					logger.warn("\nThe length of " + i
							+ "th contentUrlAry and routeAry are NOT same.");
					logger.warn(bPageUrlList.get(i));
					logger.warn("length=" + contentUrlAry.length + "\n"
							+ contentUrlList.get(i));
					logger.warn("length=" + routeAry.length + "\n"
							+ routeList.get(i));
				}
			}
		} else {
			logger.warn("The size of contentUrlList and departCityList are NOT same.");
		}
		return tbList;
	}

	private String parseName(String str) {
		return str.trim();
	}

	private String parseContent(String str) {
		return str.trim();
	}

	private String parseImgUrl(String str) {
		str = str.replace("<img src=\"", "");
		str = str.replace("\">", "");
		return str.trim();
	}

	private String parseRoutine(String str) {
		// logger.info(str);
		String pattern = "\\&nbsp;|\\s+|\\r|\n|\u3000|[\\s\\p{Zs}]";
		str = str.replaceAll(pattern, "");
		pattern = "第\\d+天";
		str = str.replaceAll(pattern, "");
		pattern = "\\(\\w+\\)";
		str = str.replaceAll(pattern, "");
		str = str.replace("--", "-");
		// logger.info(str);

		String[] routineAry = str.split("-");
		ArrayList<String> routineList = new ArrayList<String>();
		int i, j;
		String place;
		for (i = 0; i < routineAry.length; i++) {
			place = routineAry[i].trim();
			for (j = 0; j < routineList.size(); j++) {
				if (place.equalsIgnoreCase(routineList.get(j)))
					break;
			}
			if (j == routineList.size())
				routineList.add(place);
		}
		String resRoutine = "";
		for (i = 0; i < routineList.size() - 1; i++)
			resRoutine += routineList.get(i) + "-";
		resRoutine += routineList.get(i);
		// logger.info(resRoutine);
		return resRoutine;
	}

	private String parseHighlight(String str) {
		return str;
	}

	private String formatDepartCityStr(String str) {
		str = str.replaceAll("\\d", "");
		str = str.replaceAll("\\.", "");
		str = str.replace("Hbf", "");
		str = Util.replaceHtmlCodeToSpecialCharacter(str);
		str = str.trim();
		return str;
	}

	private String parseDepartCity(String str) {
		ArrayList<String> departCityList = TourMap.getInstance().retrieveDepartPlace(str);
		String departCities = "";
		if (!departCityList.isEmpty()) {
			int i = 0;
			for (; i < departCityList.size() - 1; i++)
				departCities += departCityList.get(i) + ", ";
			departCities += departCityList.get(i);
		}
		return departCities;
	}

	private String parseDepartCountry(String str) throws SQLException {
		return CategorizerUtil.selectDepartCountry(str);
	}

	private void parsePrice(String str, ArrayList<String> adultPriceList,
			ArrayList<String> childPriceList, ArrayList<String> groupPriceList) {
		adultPriceList.clear();
		childPriceList.clear();
		groupPriceList.clear();
		this.currency = Util.getCurrency(str);
		String currencySign = Util.getCurrencySign(str);
		if (currencySign.equals(Constants.CURRENCY_USD_SIGN)) {
			str = str.replaceAll("\\" + currencySign, "");
		} else {
			str = str.replaceAll(currencySign, "");
		}
		String[] ary = str.split(Constants.DELIMITER);
		for (int i = 0; i < ary.length; i++) {
			adultPriceList.add(ary[i].split(Constants.SUB_DELIMITER)[0]);
			childPriceList.add(Constants.UNKNOWN_PRICE);
			groupPriceList.add(ary[i].split(Constants.SUB_DELIMITER)[1]);
		}
	}

	private void parseGroupId(String str, ArrayList<String> groupIdList) {
		groupIdList.clear();
		String[] groupIdAry = str.split(Constants.DELIMITER);
		for (int i = 0; i < groupIdAry.length; i++) {
			groupIdList.add(groupIdAry[i].trim());
		}
	}

	private void parseTravelDate(String str, ArrayList<String> departDateList,
			ArrayList<String> endDateList) {
		departDateList.clear();
		endDateList.clear();
		String departDate;
		String endDate;
		String[] ary = str.split(Constants.DELIMITER);
		for (int i = 0; i < ary.length; i++) {
			if (Pattern.matches("\\d\\d\\.\\d\\d-\\d\\d\\.\\d\\d\\.\\d{4}",
					ary[i])) {
				departDate = ary[i].split("-")[0];
				endDate = ary[i].split("-")[1];
			} else {
				departDate = ary[i].split(Constants.SUB_DELIMITER)[0];
				endDate = ary[i].split(Constants.SUB_DELIMITER)[1];
			}
//			departDate = departDate.replaceAll("\\.20\\d\\d", "");
			departDate = departDate.split("\\.")[2] + "." + departDate.split("\\.")[1] + "."
					+ departDate.split("\\.")[0];
//			endDate = endDate.replaceAll("\\.20\\d\\d", "");
			endDate = endDate.split("\\.")[2] + "." + endDate.split("\\.")[1] + "." + endDate.split("\\.")[0];

			departDateList.add(departDate);
			endDateList.add(endDate);
		}
	}

	private void parseBooking(String str, ArrayList<String> bookingList) {
		bookingList.clear();
		String[] bookingStrAry = str.split(Constants.DELIMITER);
		for (int i = 0; i < bookingStrAry.length; i++) {
			bookingList.add(bookingStrAry[i].trim());
		}
		if (bookingList.isEmpty())
			bookingList.add("");
	}

	private void parseStatus(String str, ArrayList<String> cStatusList) {
		cStatusList.clear();
		String[] statusStrAry = str.split(Constants.DELIMITER);
		for (int i = 0; i < statusStrAry.length; i++) {
			cStatusList.add(statusStrAry[i].trim());
		}
	}

	public ArrayList<String> getbPageUrlList() {
		return bPageUrlList;
	}

	public void setbPageUrlList(ArrayList<String> bPageUrlList) {
		this.bPageUrlList = bPageUrlList;
	}

	public ArrayList<String> getbDepartCityList() {
		return cDepartCityList;
	}

	public void setbDepartCityList(ArrayList<String> cDepartCityList) {
		this.cDepartCityList = cDepartCityList;
	}

	public ArrayList<String> getbContentUrlList() {
		return bContentUrlList;
	}

	public void setbContentUrlList(ArrayList<String> bContentUrlList) {
		this.bContentUrlList = bContentUrlList;
	}

	public ArrayList<String> getcTitleList() {
		return cTitleList;
	}

	public void setcTitleList(ArrayList<String> cTitleList) {
		this.cTitleList = cTitleList;
	}

	public ArrayList<String> getcPageUrlList() {
		return cPageUrlList;
	}

	public void setcPageUrlList(ArrayList<String> cPageUrlList) {
		this.cPageUrlList = cPageUrlList;
	}

	public ArrayList<String> getcRouteList() {
		return bRouteList;
	}

	public void setcRouteList(ArrayList<String> bRouteList) {
		this.bRouteList = bRouteList;
	}

	public ArrayList<String> getcScheduleList() {
		return cScheduleList;
	}

	public void setcScheduleList(ArrayList<String> cScheduleList) {
		this.cScheduleList = cScheduleList;
	}

	public ArrayList<String> getcGroupIdList() {
		return cGroupIdList;
	}

	public void setcGroupIdList(ArrayList<String> cGroupIdList) {
		this.cGroupIdList = cGroupIdList;
	}

	public ArrayList<String> getcStatusList() {
		return cStatusList;
	}

	public void setcStatusList(ArrayList<String> cStatusList) {
		this.cStatusList = cStatusList;
	}

	public ArrayList<String> getcOrderUrlList() {
		return cOrderUrlList;
	}

	public void setcOrderUrlList(ArrayList<String> cOrderUrlList) {
		this.cOrderUrlList = cOrderUrlList;
	}

	public static String getCurrentYear() {
		return CURRENT_YEAR;
	}

	private class TourBrief {
		private String contentUrl;
		private String route;
		private String imgUrl;

		public TourBrief(String contentUrl, String route, String imgUrl) {
			this.contentUrl = contentUrl;
			this.route = route;
			this.imgUrl = imgUrl;
		}

		public String getContentUrl() {
			return contentUrl;
		}

		public void setContentUrl(String contentUrl) {
			this.contentUrl = contentUrl;
		}

		public String getRoute() {
			return route;
		}

		public void setRoute(String route) {
			this.route = route;
		}

		public String getImgUrl() {
			return this.imgUrl;
		}

		public void setImgUrl(String imgUrl) {
			this.imgUrl = imgUrl;
		}

	}

	public ArrayList<TourBrief> getTourBriefList() {
		return tourBriefList;
	}

	public void setTourBriefList(ArrayList<TourBrief> tourBriefList) {
		this.tourBriefList = tourBriefList;
	}

	public ArrayList<String> getcHighlightList() {
		return cHighlightList;
	}

	public void setcHighlightList(ArrayList<String> cHighlightList) {
		this.cHighlightList = cHighlightList;
	}

	public static void main(String[] args) {
		CTKaiyuanDe kaiyuan = new CTKaiyuanDe();
		try {
			kaiyuan.getData();
			kaiyuan.printData();
			CruiseTour.deleteSubjects(Constants.OWNER_DE_KAIYUAN);
			kaiyuan.updateDbLyouu();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
